<?php
require_once('BaseDAO.php');

class XMLFeedDAO extends BaseDAO
{
    const XML_FEED = "xml_feed";
    const XML_FEED_TABLE_MAPPING = "xml_feed_table_mapping";
    const XML_FEED_COLUMN_MAPPING = "xml_feed_column_mapping";
    const KEY_COLUMN = "key_column";
    const ACTION = "action";
    const CONFIG = "config";

    public function getXMLFeeds($userId)
    {
        $table = self::XML_FEED;
        $xmlFeeds = $this->executeQuery("SELECT * FROM $table WHERE UserId = '$userId'");
        return $this->getResultAsArray($xmlFeeds);
    }

    public function saveXMLFeed($xmlFeed)
    {
        $this->addItem(self::XML_FEED, $xmlFeed);
        return $this->getInsertedItemId();
    }

    public function saveXMLTableMapping($xmlFeedTableMapping)
    {
        $this->addItem(self::XML_FEED_TABLE_MAPPING, $xmlFeedTableMapping);
        return $this->getInsertedItemId();
    }

    public function saveXMLColumnMapping($xmlColumnMapping)
    {
        $this->addItem(self::XML_FEED_COLUMN_MAPPING, $xmlColumnMapping);
        return $this->getInsertedItemId();
    }

    public function deleteXmlFeed($xmlFeedId, $userId)
    {
        $xmlFeedTableMapping = self::XML_FEED_TABLE_MAPPING;

        $tableIds = $this->executeQuery("SELECT Id FROM $xmlFeedTableMapping WHERE XmlFeedId = $xmlFeedId");
        while ($tableId = mysqli_fetch_assoc($tableIds)) {
            $this->deleteXmlFeedColumnMapping($tableId["Id"]);
        }

        $config = $this->getConfig("TaskRunnerPath");
        $xmlFeed = $this->getXMLFeed($xmlFeedId);
        $cronExpression = $xmlFeed["CronExpression"];
//        Crontab::removeXmlJob($cronExpression, $config, $userId, $xmlFeedId);

        $this->deleteXmlFeedTableMapping($xmlFeedId);
        $this->deleteItem(self::XML_FEED, $xmlFeedId);
        $this->deleteItemWhere(self::KEY_COLUMN, Array("XmlFeedId" => $xmlFeedId));
    }

    public function getXMLFeed($xmlFeedId)
    {
        return mysqli_fetch_assoc($this->getItem(self::XML_FEED, $xmlFeedId));
    }

    private function deleteXmlFeedColumnMapping($xmlFeedTableId)
    {
        echo "Removing column mapping for XmlFeedTableId: " . $xmlFeedTableId;
        $this->deleteItemWhere(self::XML_FEED_COLUMN_MAPPING, Array("XmlFeedTableId" => $xmlFeedTableId));
    }

    private function deleteXmlFeedTableMapping($xmlFeedId)
    {
        $this->deleteItemWhere(self::XML_FEED_TABLE_MAPPING, Array("XmlFeedId" => $xmlFeedId));
    }

    public function getPrimaryXMLTableMapping($xmlFeedId)
    {
        $table = self::XML_FEED_TABLE_MAPPING;
        $xmlFeedTableMapping = $this->executeQuery("SELECT * FROM $table WHERE XmlFeedId = '$xmlFeedId' AND `Type` = 'Primary'");
        return mysqli_fetch_assoc($xmlFeedTableMapping);
    }

    public function getSecondaryXMLTableMapping($xmlFeedId)
    {
        $table = self::XML_FEED_TABLE_MAPPING;
        $xmlFeedTableMapping = $this->executeQuery("SELECT * FROM $table WHERE XmlFeedId = '$xmlFeedId' AND `Type` = 'Secondary'");
        return $this->getResultAsArray($xmlFeedTableMapping);
    }

    public function getXMLColumnMapping($xmlFeedTableMappingId)
    {
        $table = self::XML_FEED_COLUMN_MAPPING;
        $xmlFeedColumnMapping = $this->executeQuery("SELECT * FROM $table WHERE XmlFeedTableId = '$xmlFeedTableMappingId'");
        return $this->getResultAsArray($xmlFeedColumnMapping);
    }

    protected function getResultAsArray($result)
    {
        $resultArray = array();
        while ($row = mysqli_fetch_assoc($result)) {
            $resultArray[] = $row;
        }
        return $resultArray;
    }

    public function getActionName($actionId)
    {
        $result = $this->getItem(self::ACTION, $actionId);
        $action = mysqli_fetch_assoc($result);
        return $action["Name"];
    }

    public function getKeyColumn($xmlFeedId)
    {
        $table = self::KEY_COLUMN;
        $result = $this->executeQuery("SELECT * FROM $table WHERE XMLFeedId = '$xmlFeedId'");
        $column = mysqli_fetch_assoc($result);
        return $column["ColumnName"];
    }

    public function saveKeyColumn($keyColumn, $xmlFeedId)
    {
        $keyColumn = Array(
            "XmlFeedId" => $xmlFeedId,
            "ColumnName" => $keyColumn
        );
        $this->addItem(self::KEY_COLUMN, $keyColumn);
    }

    public function getMappingColumn($xmlFeedTableMappingId)
    {
        $table = self::XML_FEED_COLUMN_MAPPING;
        $result= $this->executeQuery("SELECT * FROM $table WHERE XmlFeedTableId = '$xmlFeedTableMappingId' AND IsKey = 1");
        $xmlFeedColumnMapping = mysqli_fetch_assoc($result);
        return $xmlFeedColumnMapping["DatabaseColumn"];
    }

    public function getXMLFeedList()
    {
        $table = self::XML_FEED;
        $xmlFeedList = $this->executeQuery("SELECT * FROM $table");
        return $this->getResultAsArray($xmlFeedList);
    }

    public function saveXMLColumnMappingData($data, $xmlTableMappingId)
    {
        foreach ($data as $key => $values) {
            foreach ($values as $column => $value) {
                $xmlColumnMapping = Array(
                    "XmlFeedTableId" => $xmlTableMappingId,
                    "XmlField" => $value["value"],
                    "XmlValueType" => $value["type"],
                    "DatabaseColumn" => $column,
                    "ActionId" => (empty($value["action"]) ? NULL : $value["action"]),
                    "Default" => $value["default"],
                    "DependsOn" => $value["dependsOn"],
                    "IsKey" => $value["key"],
                    "TagIndex" => $value["index"],
                );

                $this->saveXMLColumnMapping($xmlColumnMapping);
            }
        }
    }

    public function updateLastRunDate($xmlFeedId)
    {
        $table = self::XML_FEED;
        $this->executeQuery("UPDATE $table SET LastRunDate =  NOW() WHERE Id = $xmlFeedId");
    }

    public function updateXmlFeed($xmlFeed, $xmlFeedId)
    {
        $this->updateItem(self::XML_FEED, $xmlFeed, $xmlFeedId);
    }

    public function getConfig($configName)
    {
        $table = self::CONFIG;
        $result = $this->executeQuery("SELECT * FROM $table WHERE `Name` = '$configName'");
        $config = mysqli_fetch_assoc($result);
        return $config["Value"];
    }

//    public function getFullXMLFeed($xmlFeedId)
//    {
//        $table = self::XML_FEED;
//        $databaseTable = DatabaseDAO::USER_DATABASE;
//        $xmlFeedList = $this->executeQuery("SELECT f.*, d.DatabaseName FROM $table f LEFT JOIN $databaseTable d ON f.DatabaseId = d.Id");
//        return $this->getResultAsArray($xmlFeedList);
//    }
}

?>